/**
 * 批量重算优化脚本
 * 解决大批量数据导入时的429速率限制问题
 */

const sqlite3 = require('sqlite3').verbose();
const path = require('path');

// 数据库连接
const dbPath = path.join(__dirname, '../pph_data.db');
const db = new sqlite3.Database(dbPath);

/**
 * 检查重算任务状态
 */
async function checkRecomputeJobs() {
  return new Promise((resolve, reject) => {
    db.all(`
      SELECT 
        id, 
        status, 
        total, 
        processed, 
        updated,
        batch_size,
        started_at,
        finished_at,
        error
      FROM recompute_jobs 
      ORDER BY started_at DESC 
      LIMIT 10
    `, (err, rows) => {
      if (err) reject(err);
      else resolve(rows);
    });
  });
}

/**
 * 优化重算任务配置
 */
async function optimizeRecomputeConfig() {
  console.log('🔧 优化重算任务配置...');
  
  // 设置环境变量优化
  process.env.RECOMPUTE_BATCH_SIZE = '50';  // 减小批次大小
  process.env.RECOMPUTE_THROTTLE_MS = '2000'; // 增加节流时间到2秒
  
  console.log('✅ 配置已优化:');
  console.log(`   - 批次大小: ${process.env.RECOMPUTE_BATCH_SIZE}`);
  console.log(`   - 节流时间: ${process.env.RECOMPUTE_THROTTLE_MS}ms`);
}

/**
 * 重启失败的重算任务
 */
async function restartFailedJobs() {
  return new Promise((resolve, reject) => {
    db.run(`
      UPDATE recompute_jobs 
      SET status = 'queued', 
          error = NULL,
          started_at = NULL,
          finished_at = NULL
      WHERE status = 'failed'
    `, function(err) {
      if (err) reject(err);
      else {
        console.log(`🔄 重启了 ${this.changes} 个失败的重算任务`);
        resolve(this.changes);
      }
    });
  });
}

/**
 * 清理过期的重算任务
 */
async function cleanupOldJobs() {
  return new Promise((resolve, reject) => {
    // 删除7天前的已完成任务
    db.run(`
      DELETE FROM recompute_jobs 
      WHERE status IN ('completed', 'failed') 
        AND datetime(finished_at) < datetime('now', '-7 days')
    `, function(err) {
      if (err) reject(err);
      else {
        console.log(`🧹 清理了 ${this.changes} 个过期的重算任务`);
        resolve(this.changes);
      }
    });
  });
}

/**
 * 分析重算性能
 */
async function analyzePerformance() {
  return new Promise((resolve, reject) => {
    db.all(`
      SELECT 
        AVG(total) as avg_total,
        AVG(processed) as avg_processed,
        AVG(updated) as avg_updated,
        AVG(batch_size) as avg_batch_size,
        AVG(
          CASE 
            WHEN started_at IS NOT NULL AND finished_at IS NOT NULL 
            THEN (julianday(finished_at) - julianday(started_at)) * 24 * 60 * 60 
            ELSE NULL 
          END
        ) as avg_duration_seconds,
        COUNT(*) as total_jobs,
        SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) as completed_jobs,
        SUM(CASE WHEN status = 'failed' THEN 1 ELSE 0 END) as failed_jobs
      FROM recompute_jobs 
      WHERE started_at > datetime('now', '-24 hours')
    `, (err, rows) => {
      if (err) reject(err);
      else resolve(rows[0]);
    });
  });
}

/**
 * 主函数
 */
async function main() {
  try {
    console.log('📊 批量重算优化工具');
    console.log('='.repeat(50));
    
    // 1. 检查当前任务状态
    console.log('\n1️⃣ 检查重算任务状态...');
    const jobs = await checkRecomputeJobs();
    console.log(`📋 最近10个任务:`);
    jobs.forEach((job, index) => {
      const duration = job.started_at && job.finished_at 
        ? Math.round((new Date(job.finished_at) - new Date(job.started_at)) / 1000)
        : 'N/A';
      console.log(`   ${index + 1}. ${job.id} - ${job.status} (${job.processed}/${job.total}) - ${duration}s`);
    });
    
    // 2. 性能分析
    console.log('\n2️⃣ 性能分析...');
    const perf = await analyzePerformance();
    console.log(`📈 24小时内统计:`);
    console.log(`   - 总任务数: ${perf.total_jobs}`);
    console.log(`   - 完成任务: ${perf.completed_jobs}`);
    console.log(`   - 失败任务: ${perf.failed_jobs}`);
    console.log(`   - 平均处理记录数: ${Math.round(perf.avg_processed || 0)}`);
    console.log(`   - 平均更新记录数: ${Math.round(perf.avg_updated || 0)}`);
    console.log(`   - 平均耗时: ${Math.round(perf.avg_duration_seconds || 0)}秒`);
    console.log(`   - 平均批次大小: ${Math.round(perf.avg_batch_size || 0)}`);
    
    // 3. 优化配置
    console.log('\n3️⃣ 优化配置...');
    await optimizeRecomputeConfig();
    
    // 4. 重启失败任务
    console.log('\n4️⃣ 重启失败任务...');
    await restartFailedJobs();
    
    // 5. 清理过期任务
    console.log('\n5️⃣ 清理过期任务...');
    await cleanupOldJobs();
    
    console.log('\n✅ 优化完成！');
    console.log('\n💡 建议:');
    console.log('   - 对于超过1万条记录的批量导入，建议分批处理');
    console.log('   - 监控系统负载，必要时进一步调整批次大小和节流时间');
    console.log('   - 考虑在低峰期进行大批量重算操作');
    
  } catch (error) {
    console.error('❌ 优化过程中出错:', error.message);
  } finally {
    db.close();
  }
}

// 如果直接运行此脚本
if (require.main === module) {
  main();
}

module.exports = {
  checkRecomputeJobs,
  optimizeRecomputeConfig,
  restartFailedJobs,
  cleanupOldJobs,
  analyzePerformance
};
